import express, {Request, Response} from 'express';
import mysql, {Connection} from 'mysql';
import moment from 'moment';

const app = express();
const port = 3000;

app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// 创建与MySQL数据库的连接
const connection: Connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'wule'
});
// 连接到MySQL数据库
connection.connect((err: any) => {
    if (err) throw err;
    console.log('连接成功!!');
});

/*用户表单ts类型*/
interface User {
    id?: number
    name: string
    birthday: Date
    sex: number
    address: string
}

// 启动服务器
app.listen(port, () => {
    console.log(`服务器启动,服务端口: ${port},访问地址:http://localhost:${port}/test`);
});

// 增加数据的接口
app.post('/api/addUser', (req: Request, res: Response) => {
    const user = req.body.user;
    user.birthday = moment(user.birthday).format('YYYY-MM-DD');
    console.log("接受到要增加的用户信息为:",user)
    connection.query('INSERT INTO user SET ?', {username:user.username,birthday:user.birthday,sex:user.sex,address:user.address}, (err: any, result: any) => {
        if (err) {
            res.status(500).json({message: err});
        } else {
            res.json({code: 200, message:`添加用户成功!`});
        }
    });
});


// 修改数据的接口
app.put('/api/updateUserInfo', (req: Request, res: Response) => {
    const user = req.body;
    user.birthday = moment(user.birthday).format('YYYY-MM-DD');
    console.log("接受到的修改后端用户信息为:",user)
    connection.query('UPDATE user SET username = ?, birthday = ?, sex = ?, address = ? WHERE id = ?', [user.username, user.birthday,user.sex, user.address,user.id], (err: any, result: any) => {
        if (err) {
            res.status(500).json({message: err});
        } else {
            res.json({code: 200, message:`修改id为${user.id}的用户信息成功`});
        }
    });
});

// 删除数据的接口
app.delete('/api/delete/:id', (req: Request, res: Response) => {
    const id = req.params.id;
    connection.query('DELETE FROM user WHERE id = ?', id, (err: any, result: any) => {
        if (err) {
            res.status(500).json({message: err});
        } else {
            res.json({code: 200,message:`您已成功删除id为${id}的数据!!`});
        }
    });
});


// 查找用户总数据的接口
app.get('/api/userInfo/:pageNo/:pageSize', (req: Request, res: Response) => {
    const pageNo: number = parseInt(req.params.pageNo);
    const pageSize: number = parseInt(req.params.pageSize);
    const username: string = req.query.username as string;


    console.log("请求的页码是:", pageNo, ",一页的数据量为:", pageSize, "要模糊搜索的用户名是:", username)
    const startIndex = (pageNo - 1) * pageSize;
    // 查询用户总个数
    let countSql = `SELECT COUNT(*) as count FROM user`;
    let sql = `SELECT * FROM user LIMIT ${startIndex}, ${pageSize}`;
    //根据模糊用户名判断是否翻页,即模糊用户名是否为空判断sql语句的写法
    if (username != "") {
        countSql = `SELECT COUNT(*) as count FROM user WHERE username LIKE '%${username}%'`;
        sql = `SELECT * FROM user WHERE username LIKE '%${username}%' LIMIT ${startIndex}, ${pageSize}`;
    }

    connection.query(countSql, (err: any, countResult: any) => {
        if (err) {
            res.status(500).json({message: err});
        } else {
            const total = countResult[0].count;
            connection.query(sql, (err: any, result: User[]) => {
                if (err) {
                    res.status(500).json({message: err});
                } else {
                    res.json({code: 200, userInfo: result, total: total});
                }
            })
        }
    })
})


// 测试后端接口
app.get('/test', (req: Request, res: Response) => {
    res.send("SUCCESS!!!");
});


